

-- All Managed Entities Types
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_managedentitytype]'))
DROP PROC [dbo].[sp_syscentered_managedentitytype]

Go

CREATE PROC sp_syscentered_managedentitytype
AS

Select ManagedEntityTypeRowId, ManagementPackRowId, ManagedEntityTypeSystemName
From vManagedEntityType with (nolock)

Go


-- All Managed Entities
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_managedentity_fortype]'))
DROP PROC [dbo].[sp_syscentered_managedentity_fortype]

Go

CREATE PROC sp_syscentered_managedentity_fortype
(
@ManagedEntityTypeId int
)
As
Select ManagedEntityRowId,ManagementGroupRowId, TopLevelHostManagedEntityRowId, FullName,Path,ManagedEntityDefaultName
From vManagedEntity with (nolock)
Where  ManagedEntityTypeRowId = @ManagedEntityTypeId

Go


-- All Managed Entities - By TypeName
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_managedentity_fortypename]'))
DROP PROC [dbo].[sp_syscentered_managedentity_fortypename]

Go

CREATE PROC sp_syscentered_managedentity_fortypename
(
@ManagedEntityTypeSystemName varchar(256)
)
As
Select ManagedEntityRowId,ManagementGroupRowId, TopLevelHostManagedEntityRowId, FullName,Path,ManagedEntityDefaultName
From vManagedEntity A with (nolock)
Join vManagedEntityType B with (nolock)
On A.ManagedEntityTypeRowId = B.ManagedEntityTypeRowId
Where  B.ManagedEntityTypeSystemName = @ManagedEntityTypeSystemName
Order By ManagedEntityDefaultName



Go


-- PerfMon : Counters - Object
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_perfcounters_object_forobject]'))
DROP PROC [dbo].[sp_syscentered_perfcounters_object_forobject]

Go

CREATE PROC [sp_syscentered_perfcounters_object_forobject]
(
@ParentManagedEntityRowId int,
@StartDateTime datetime,
@EndDateTime datetime
)
As


Select ObjectName
From Perf.vPerfDaily A with (nolock)
Join vw_syscentered_ManagedEntityRelationship B
On A.ManagedEntityRowId = B.ManagedEntityRowId
Join vPerformanceRuleInstance C with (nolock)
On A.PerformanceRuleInstanceRowId = C.PerformanceRuleInstanceRowId
Join vPerformanceRule D with (nolock)
On D.RuleRowId = C.RuleRowId
Join vManagedEntity E with (nolock)
On A.ManagedEntityRowId = E.ManagedEntityRowId
Where E.TopLevelHostManagedEntityRowId = @ParentManagedEntityRowId
And ((A.DateTime > @StartDateTime
And A.DateTime < GETDATE() + 2)
		Or A.DateTime > (@EndDateTime -2)
		)
Group By ObjectName
Order By ObjectName


Go

-- PerfMon : Counters - Counter
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_perfcounters_counter_forobject]'))
DROP PROC [dbo].[sp_syscentered_perfcounters_counter_forobject]

Go

CREATE PROC [sp_syscentered_perfcounters_counter_forobject]
	(
	@ParentManagedEntityRowId int,
	@ObjectName varchar(100),
	@StartDateTime datetime,
	@EndDateTime datetime
	)
As

Select CounterName
From Perf.vPerfDaily A with (nolock)
	Join vw_syscentered_ManagedEntityRelationship B
		On A.ManagedEntityRowId = B.ManagedEntityRowId
	Join vPerformanceRuleInstance C with (nolock)
		On A.PerformanceRuleInstanceRowId = C.PerformanceRuleInstanceRowId
	Join vPerformanceRule D with (nolock)
		On D.RuleRowId = C.RuleRowId	
	Join vManagedEntity E with (nolock)
		On A.ManagedEntityRowId = E.ManagedEntityRowId
Where E.TopLevelHostManagedEntityRowId = @ParentManagedEntityRowId
	And ((A.DateTime > @StartDateTime
			And A.DateTime < GETDATE() + 2)
		Or A.DateTime > (@EndDateTime -2)
		)
	And ObjectName = @ObjectName		
Group By CounterName
Order By CounterName


Go



-- PerfMon : Counters - Counter
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_perfcounters_instance_forcounter]'))
DROP PROC [dbo].[sp_syscentered_perfcounters_instance_forcounter]

Go

CREATE PROC [sp_syscentered_perfcounters_instance_forcounter]
	(
	@ParentManagedEntityRowId int,
	@ObjectName varchar(100),
	@CounterName varchar(100),
	@StartDateTime datetime,
	@EndDateTime datetime
	)
As 


Select InstanceName
From Perf.vPerfDaily A with (nolock)
	Join vw_syscentered_ManagedEntityRelationship B
		On A.ManagedEntityRowId = B.ManagedEntityRowId
	Join vPerformanceRuleInstance C with (nolock)
		On A.PerformanceRuleInstanceRowId = C.PerformanceRuleInstanceRowId
	Join vPerformanceRule D with (nolock)
		On D.RuleRowId = C.RuleRowId	
	Join vManagedEntity E with (nolock)
		On A.ManagedEntityRowId = E.ManagedEntityRowId
Where E.TopLevelHostManagedEntityRowId = @ParentManagedEntityRowId
	And ((A.DateTime > @StartDateTime
			And A.DateTime < GETDATE() + 2)
		Or A.DateTime > (@EndDateTime -2)
		)
	And ObjectName = @ObjectName	
	And CounterName = @CounterName	
Group By InstanceName

IF @@ROWCOUNT = 0
	Select '' As InstanceName
Go


-- Get Child ManagedEntities
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_syscentered_ManagedEntityRelationship]'))
DROP VIEW [dbo].[vw_syscentered_ManagedEntityRelationship]

Go

CREATE VIEW vw_syscentered_ManagedEntityRelationship
As

Select A.ManagedEntityRowId, A.TopLevelHostManagedEntityRowId As ParentManagedEntityRowId, B.DisplayName As ObjectDisplayName
From vManagedEntity A with (nolock)
	Join vManagedEntity B with (nolock)
		On A.TopLevelHostManagedEntityRowId = B.ManagedEntityRowId
		

Go

-- PerfMon : PerfData
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_perf_hourly]'))
DROP PROC [dbo].[sp_syscentered_perf_hourly]

Go

CREATE PROC sp_syscentered_perf_hourly
	(
	@RootManagedEntityRowId int, 
	@PerfRuleInstanceRowId int,
	@StartDateTime datetime,
	@EndDateTime datetime
	)
As

DECLARE @DateTimeOffset int
Select @DateTimeOffset =  DateDiff(HOUR,GETUTCDATE(),GETDATE())

SET @StartDateTime = DATEADD(HOUR,@DateTimeOffset,@StartDateTime) 
SET @EndDateTime = DATEADD(HOUR,@DateTimeOffset,@EndDateTime)

Select DateAdd(HOUR,@DateTimeOffset,DateTime) As DateTime, InstanceName, AverageValue As Value, MinValue, MaxValue, StandardDeviation
From Perf.vPerfHourly A with (nolock)
	Join vw_syscentered_ManagedEntityRelationship B
		On A.ManagedEntityRowId = B.ManagedEntityRowId
	Join vPerformanceRuleInstance C
		On A.PerformanceRuleInstanceRowId = C.PerformanceRuleInstanceRowId
Where A.PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
	And B.ParentManagedEntityRowId = @RootManagedEntityRowId
	And DateTime > @StartDateTime
	And DateTime < @EndDateTime
Order By A.DateTime

Go




-- PerfMon : PerfData : Multi Parameter
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_perf_multi_hourly]'))
DROP PROC [dbo].[sp_syscentered_perf_multi_hourly]

Go

CREATE PROC sp_syscentered_perf_multi_hourly
	(
	@RootManagedEntityRowId varchar(max), 
	@PerfRuleInstanceRowId int,
	@StartDateTime datetime,
	@EndDateTime datetime
	)
As

DECLARE @DateTimeOffset int
Select @DateTimeOffset =  DateDiff(HOUR,GETUTCDATE(),GETDATE())

SET @StartDateTime = DATEADD(HOUR,@DateTimeOffset,@StartDateTime) 
SET @EndDateTime = DATEADD(HOUR,@DateTimeOffset,@EndDateTime)

Select B.ObjectDisplayName, DateAdd(HOUR,@DateTimeOffset,DateTime) As DateTime, InstanceName, AverageValue As Value, MinValue, MaxValue, StandardDeviation
From Perf.vPerfHourly A with (nolock)
	Join vw_syscentered_ManagedEntityRelationship B
		On A.ManagedEntityRowId = B.ManagedEntityRowId
	Join vPerformanceRuleInstance C
		On A.PerformanceRuleInstanceRowId = C.PerformanceRuleInstanceRowId
Where A.PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
	And B.ParentManagedEntityRowId IN (@RootManagedEntityRowId)
	And DateTime > @StartDateTime
	And DateTime < @EndDateTime
Order By A.DateTime

Go


-- PerfMon : PerfDaily
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_perf_daily]'))
DROP PROC [dbo].[sp_syscentered_perf_daily]

Go

CREATE PROC sp_syscentered_perf_daily
	(
	@RootManagedEntityRowId int, 
	@PerfRuleInstanceRowId int,
	@StartDateTime datetime,
	@EndDateTime datetime
	)
As

DECLARE @DateTimeOffset int
Select @DateTimeOffset =  DateDiff(HOUR,GETUTCDATE(),GETDATE())

SET @StartDateTime = DATEADD(HOUR,@DateTimeOffset,@StartDateTime) 
SET @EndDateTime = DATEADD(HOUR,@DateTimeOffset,@EndDateTime)

Select DateAdd(HOUR,@DateTimeOffset,DateTime) As DateTime, InstanceName, AverageValue As Value, MinValue, MaxValue, StandardDeviation
From Perf.vPerfDaily A with (nolock)
	Join vw_syscentered_ManagedEntityRelationship B
		On A.ManagedEntityRowId = B.ManagedEntityRowId
	Join vPerformanceRuleInstance C
		On A.PerformanceRuleInstanceRowId = C.PerformanceRuleInstanceRowId
Where A.PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
	And B.ParentManagedEntityRowId = @RootManagedEntityRowId
	And DateTime > @StartDateTime
	And DateTime < @EndDateTime
Order By A.DateTime

Go



-- PerfMon : PerfDaily
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_perf_multi_daily]'))
DROP PROC [dbo].[sp_syscentered_perf_multi_daily]

Go

CREATE PROC sp_syscentered_perf_multi_daily
	(
	@RootManagedEntityRowId varchar(max), 
	@PerfRuleInstanceRowId int,
	@StartDateTime datetime,
	@EndDateTime datetime
	)
As

DECLARE @DateTimeOffset int
Select @DateTimeOffset =  DateDiff(HOUR,GETUTCDATE(),GETDATE())

SET @StartDateTime = DATEADD(HOUR,@DateTimeOffset,@StartDateTime) 
SET @EndDateTime = DATEADD(HOUR,@DateTimeOffset,@EndDateTime)

Select B.ObjectDisplayName, DateAdd(HOUR,@DateTimeOffset,DateTime) As DateTime, InstanceName, AverageValue As Value, MinValue, MaxValue, StandardDeviation
From Perf.vPerfDaily A with (nolock)
	Join vw_syscentered_ManagedEntityRelationship B
		On A.ManagedEntityRowId = B.ManagedEntityRowId
	Join vPerformanceRuleInstance C
		On A.PerformanceRuleInstanceRowId = C.PerformanceRuleInstanceRowId
Where A.PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
	And B.ParentManagedEntityRowId IN (@RootManagedEntityRowId)
	And DateTime > @StartDateTime
	And DateTime < @EndDateTime
Order By A.DateTime

Go

-- PerfMon : PerfRaw
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_perf_raw]'))
DROP PROC [dbo].[sp_syscentered_perf_raw]

Go

CREATE PROC sp_syscentered_perf_raw
	(
	@RootManagedEntityRowId int, 
	@PerfRuleInstanceRowId int,
	@StartDateTime datetime,
	@EndDateTime datetime
	)
As

DECLARE @DateTimeOffset int
Select @DateTimeOffset =  DateDiff(HOUR,GETUTCDATE(),GETDATE())

SET @StartDateTime = DATEADD(HOUR,@DateTimeOffset,@StartDateTime) 
SET @EndDateTime = DATEADD(HOUR,@DateTimeOffset,@EndDateTime)

Select DateAdd(HOUR,@DateTimeOffset,DateTime) As DateTime, InstanceName, SampleValue As Value
From Perf.vPerfRaw A with (nolock)
	Join vw_syscentered_ManagedEntityRelationship B
		On A.ManagedEntityRowId = B.ManagedEntityRowId
	Join vPerformanceRuleInstance C
		On A.PerformanceRuleInstanceRowId = C.PerformanceRuleInstanceRowId
Where A.PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
	And B.ParentManagedEntityRowId = @RootManagedEntityRowId
	And DateTime > @StartDateTime
	And DateTime < @EndDateTime
Order By A.DateTime


Go


-- PerfMon : PerfRaw
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_perf_multi_raw]'))
DROP PROC [dbo].[sp_syscentered_perf_multi_raw]

Go

CREATE PROC sp_syscentered_perf_multi_raw
	(
	@RootManagedEntityRowId varchar(max), 
	@PerfRuleInstanceRowId int,
	@StartDateTime datetime,
	@EndDateTime datetime
	)
As

DECLARE @DateTimeOffset int
Select @DateTimeOffset =  DateDiff(HOUR,GETUTCDATE(),GETDATE())

SET @StartDateTime = DATEADD(HOUR,@DateTimeOffset,@StartDateTime) 
SET @EndDateTime = DATEADD(HOUR,@DateTimeOffset,@EndDateTime)

Select B.ObjectDisplayName, DateAdd(HOUR,@DateTimeOffset,DateTime) As DateTime, InstanceName, SampleValue As Value
From Perf.vPerfRaw A with (nolock)
	Join vw_syscentered_ManagedEntityRelationship B
		On A.ManagedEntityRowId = B.ManagedEntityRowId
	Join vPerformanceRuleInstance C
		On A.PerformanceRuleInstanceRowId = C.PerformanceRuleInstanceRowId
Where A.PerformanceRuleInstanceRowId = @PerfRuleInstanceRowId
	And B.ParentManagedEntityRowId IN (@RootManagedEntityRowId)
	And DateTime > @StartDateTime
	And DateTime < @EndDateTime
Order By A.DateTime


Go

-- PerfMon : PerfGeneral
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_perf]'))
DROP PROC [dbo].[sp_syscentered_perf]

Go

CREATE PROC [sp_syscentered_perf]
	(
	@RootManagedEntityRowId int, 
	@PerfRuleInstanceRowId int,
	@StartDateTime datetime,
	@EndDateTime datetime,
	@AggregationType varchar(50) = 'Hourly'
	)
As



	IF @AggregationType =  'Raw' BEGIN
		exec sp_syscentered_perf_raw @RootManagedEntityRowId, @PerfRuleInstanceRowId, @StartDateTime, @EndDateTime
		END
	ELSE IF @AggregationType = 'Daily' BEGIN
		exec sp_syscentered_perf_daily @RootManagedEntityRowId, @PerfRuleInstanceRowId, @StartDateTime, @EndDateTime
		END
	ELSE BEGIN
		exec sp_syscentered_perf_hourly @RootManagedEntityRowId, @PerfRuleInstanceRowId, @StartDateTime, @EndDateTime
	END
	



Go


-- PerfMon : PerfGeneral
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_perf_multi]'))
DROP PROC [dbo].[sp_syscentered_perf_multi]

Go

CREATE PROC [sp_syscentered_perf_multi]
	(
	@RootManagedEntityRowId varchar(max), 
	@PerfRuleInstanceRowId int,
	@StartDateTime datetime,
	@EndDateTime datetime,
	@AggregationType varchar(50) = 'Hourly'
	)
As

	IF @AggregationType =  'Raw' BEGIN
		exec sp_syscentered_perf_multi_raw @RootManagedEntityRowId, @PerfRuleInstanceRowId, @StartDateTime, @EndDateTime
		END
	ELSE IF @AggregationType = 'Daily' BEGIN
		exec sp_syscentered_perf_multi_daily @RootManagedEntityRowId, @PerfRuleInstanceRowId, @StartDateTime, @EndDateTime
		END
	ELSE BEGIN
		exec sp_syscentered_perf_multi_hourly @RootManagedEntityRowId, @PerfRuleInstanceRowId, @StartDateTime, @EndDateTime
	END
	



Go


-- PerfRuleInstanceRowId
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_perfruleinstance_getfromobjectcounterinstance]'))
DROP PROC [dbo].[sp_syscentered_perfruleinstance_getfromobjectcounterinstance]

Go

CREATE PROC sp_syscentered_perfruleinstance_getfromobjectcounterinstance
	(
	@ObjectName varchar(255),
	@CounterName varchar(255),
	@InstanceName varchar(255)
	)
As

If @InstanceName is null Begin
	Select B.PerformanceRuleInstanceRowId
	From vPerformanceRule A
		Join vPerformanceRuleInstance B
			On A.RuleRowId = B.RuleRowId
	Where ObjectName = @ObjectName	
		And CounterName = @CounterName
End
Else
Begin 
	Select B.PerformanceRuleInstanceRowId
	From vPerformanceRule A
		Join vPerformanceRuleInstance B
			On A.RuleRowId = B.RuleRowId
	Where ObjectName = @ObjectName	
		And CounterName = @CounterName
		And InstanceName = @InstanceName

End

GO

-- Collected Perfmon counters for object
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[sp_syscentered_collectedperfmoncounters_formanagedentity]'))
DROP PROC [dbo].[sp_syscentered_collectedperfmoncounters_formanagedentity]

Go	

CREATE PROC sp_syscentered_collectedperfmoncounters_formanagedentity
	(@ParentManagedEntityRowId int)
AS

Select E.Path, ObjectName, CounterName, InstanceName, G.ManagementPackDefaultName, Convert(varchar(10),Min(A.DateTime),101) As MinCollectedDay, Convert(varchar(10),Max(A.DateTime),101) As MaxCollectedDay, Count(*) As Iterations, SUM(A.SampleCount) As TotalSampleCount, AVG(AverageValue) As AverageValue, Max(MaxValue) As MaxValue, Min(MinValue) As MinValue
From Perf.vPerfDaily A with (nolock)
	Join vw_syscentered_ManagedEntityRelationship B
		On A.ManagedEntityRowId = B.ManagedEntityRowId
	Join vPerformanceRuleInstance C with (nolock)
		On A.PerformanceRuleInstanceRowId = C.PerformanceRuleInstanceRowId
	Join vPerformanceRule D with (nolock)
		On D.RuleRowId = C.RuleRowId	
	Join vManagedEntity E with (nolock)
		On A.ManagedEntityRowId = E.ManagedEntityRowId
	Join vRule F
		On F.RuleRowId = C.RuleRowId
	Join vManagementPack G
		On F.ManagementPackRowId = G.ManagementPackRowId
Where E.TopLevelHostManagedEntityRowId = @ParentManagedEntityRowId
Group By E.Path, ObjectName, CounterName, InstanceName, ManagementPackDefaultName
Order By E.Path, ObjectName, CounterName, InstanceName

GO